﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;

namespace AddProduct
{
    class AddToNorthWind
    {
        public static void InsertToProducts(SqlConnection dbCon , string ProductName, int? SupplierID, int?CategoryID ,
            string QuantityPerUnit, decimal? UnitPrice, int? UnitsInStock,int?UnitsOnOrder,
            int? ReorderLevel,bool Discontinued) 
        {
            if(ProductName.Length>40||QuantityPerUnit.Length>20)
            {
                throw new ArgumentOutOfRangeException("Invalid string LEnght");
            }
            SqlCommand cmdInsertProject = new SqlCommand("INSERT INTO Products(ProductName, SupplierID, CategoryID,"+
                                "QuantityPerUnit,  UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued ) "+ 
                                "VALUES (@ProductName, @SupplierID, @CategoryID, @QuantityPerUnit, @UnitPrice, "+
                                "@UnitsInStock, @UnitsOnOrder,  @ReorderLevel, @Discontinued )", dbCon);
            cmdInsertProject.Parameters.Add(new SqlParameter("@ProductName", ProductName));
            cmdInsertProject.Parameters.Add(new SqlParameter("@SupplierID", SupplierID));
            cmdInsertProject.Parameters.Add(new SqlParameter("@CategoryID", CategoryID));
            cmdInsertProject.Parameters.Add(new SqlParameter("@QuantityPerUnit", QuantityPerUnit));
            cmdInsertProject.Parameters.Add(new SqlParameter("@UnitPrice", UnitPrice));
            cmdInsertProject.Parameters.Add(new SqlParameter("@UnitsInStock", UnitsInStock));
            cmdInsertProject.Parameters.Add(new SqlParameter("@UnitsOnOrder", UnitsOnOrder));
            cmdInsertProject.Parameters.Add(new SqlParameter("@ReorderLevel", ReorderLevel));
            cmdInsertProject.Parameters.Add(new SqlParameter("@Discontinued", Discontinued));
            cmdInsertProject.ExecuteNonQuery();
            Console.WriteLine( "Success");
        }

        static void Main(string[] args)
        {
            SqlConnection dbCon = new SqlConnection(
              "Server=.\\SQLEXPRESS; " +
              "Database=Northwind; " +
              "Integrated Security=true");
            dbCon.Open();
            using (dbCon)
            {
                InsertToProducts(dbCon, "pr", 1, 1, "12", 12, 12, 11, 11, true);
            }
        }
    }
}
